﻿using IDAL;
using Model;
using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class FrStudentManage_DAL : IFrStudentManage_IDAL
    {
        #region 查询
        //查询科目
        List<Course_info_MD> IFrStudentManage_IDAL.SCourse_info()
        {
            SqlDataReader sqlData = SQLHepler.GetDataReader("select *from Course_info");
            List<Course_info_MD> classes = new List<Course_info_MD>();
            while (sqlData.Read())
            {
                classes.Add(new Course_info_MD()
                {
                     CourseId = sqlData["CourseId"].ToString(),
                    CourseName = sqlData["CourseName"].ToString()

                });
            }
            sqlData.Close();
            return classes;
        }
        //查询班级
        List<StudentClass_MD> IFrStudentManage_IDAL.Sclass()
        {
            SqlDataReader sqlData = SQLHepler.GetDataReader("select *from Class_info");
            List<StudentClass_MD> classes = new List<StudentClass_MD>();
            while (sqlData.Read())
            {
                classes.Add(new StudentClass_MD()
                {
                    ClassId = Convert.ToInt32(sqlData["ClassId"]),
                    ClassName = sqlData["ClassName"].ToString()

                });
            }
            sqlData.Close();
            return classes;
        }
        //查询考生
        List<Students_MD> IFrStudentManage_IDAL.Students(string path)
        {
            SqlDataReader reader = SQLHepler.GetDataReader($"select *from View_1 {path}");
            List<Students_MD> Students = new List<Students_MD>();
            while (reader.Read())
            {
                Students.Add(new Students_MD()
                {
                    StudentId = Convert.ToInt32(reader["StudentId"]),
                    StuImage = reader["StuImage"].ToString(),
                    Gender = reader["Gender"].ToString(),
                    StudentName = reader["StudentName"].ToString(),
                    StudentAddress = reader["StudentAddress"].ToString(),
                    StudentIdNo = reader["StudentIdNo"].ToString(),
                    Birthday = reader["Birthday"].ToString(),
                    Age = Convert.ToInt32(reader["年龄"]),
                    PhoneNumber = reader["PhoneNumber"].ToString(),
                  
                    ClassName = reader["ClassName"].ToString()
                });
            }
            reader.Close();
            return Students;
        }
        #endregion
        #region 更新
      
        //修改
        public bool AlterStudent(Students_MD Students,string id)
        {
            try
            {
                StringBuilder stringBuilder = new StringBuilder($"update Examinee_info set StudentName='{Students.StudentName}', ");
                stringBuilder.Append($"Gender='{Students.Gender}',Birthday='{Students.Birthday}',StudentIdNo='{Students.StudentIdNo}'");
                
                stringBuilder.Append($",StuImage='{Students.StuImage}',PhoneNumber='{Students.PhoneNumber}',StudentAddress='{Students.StudentAddress}',ClassId='{Students.ClassId}'  where StudentId={id}");
                SQLHepler.Update(stringBuilder.ToString());
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
          
        }
        //添加

        public int AddStudent(Students_MD Students)
        {
            try
            {

                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("insert into Examinee_info(StudentName,Gender,Birthday,StudentIdNo,StuImage,PhoneNumber,StudentAddress,ClassId) values");
                stringBuilder.Append($"('{Students.StudentName}','{Students.Gender}','{Students.Birthday}','{Students.StudentIdNo}','{Students.StuImage}','{Students.PhoneNumber}','{Students.StudentAddress}','{Students.ClassId}') select @@identity ;");
                
                
                return Convert.ToInt32( SQLHepler.GetScalar(stringBuilder.ToString()));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        #endregion
    
        #region 批量查询
        List<Students_MD> IFrStudentManage_IDAL.GetStudentsFromExcel(string path)
        {
            List<Students_MD> Students = new List<Students_MD>();
            string sql = "select *from [Students$]";
            try
            {
                OleDbDataReader s = OleDbHelper.GetDataReader(sql, path);
                while (s.Read())
                {
                    if (s["姓名"].ToString() == "")
                    {
                        break;
                    }
                    Students.Add(new Model.Students_MD()
                    {
                        StudentName = s["姓名"].ToString(),
                        Gender = s["性别"].ToString(),
                        Birthday = s["出生日期"].ToString(),
                        StudentIdNo = s["身份证号"].ToString(),
                        PhoneNumber = s["电话号码"].ToString(),
                        StudentAddress = s["家庭住址"].ToString(),
                        ClassId = Convert.ToInt32(s["班级编号"]),
                        Age = SQLHepler.GetDateTime().Year - ((DateTime)s["出生日期"]).Year
                    });
                }
                s.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Excel 获取数据异常: " + ex.Message);
            }
            return Students;
        }

     
        #endregion
    }
}
